Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Welcome back. Now that we have the skeleton for the administration back-end application created, it's time to start filling in some of the pieces.

In today's lesson, you'll learn how to insert data from an HTML form into a database. While you may have done this in the past, today we'll add a few twists to it. First, you'll look at what you should do to prepare your data properly before entering it into the database. A few things can send your SQL statements into a tizzy, and it's your job to use the proper PHP code to block them.

After that, you'll see how to store images in the database tables. The Food Store application allows a manager to post an image for a product that will appear in the catalog pages for customers to see. You'll track the necessary code that allows our manager to specify an image file on his or her PC to upload to the Web site.

Finally, once the manager uploads an image file, you must convert your application into a standard size and save it in your database. You'll see what it takes to perform all of these functions in today's lesson.

This should give us a good start to the administration functions for our store Web site. Let's move on to Chapter 2 and get started.

Creating Product Categories

Before your manager can enter new products in the store, you'll need to create Web pages that allow your store managers to create new categories to classify the products. Your Food Store application uses the standard HTML form for this. This method of allowing Web page visitors to post data has been around for as long as the World Wide Web.

Once a Web site visitor fills in the form and clicks a Submit button, the HTML code sends the data to another page for processing. The PHP language provides three methods for extracting data from HTML forms:

• The $_GET[] array variable for forms using the HTML GET method.
• The $_POST[] array variable for forms using the HTML POST method.
• The $_REQUEST[] array variable for forms using either method.

When the PHP code retrieves the new category name from the HTML form, it'll need to insert it into the categories table.

To get to the HTML form Web page, you have a link in the navigation area (the Add New Category link). It uses the URL admin.php?content=newcat, which points to the admin.php file, using a content value of newcat.

The newcat.inc.php creates a simple HTML form that allows the manager to enter the name of a new category. To create the code file, just follow these steps:

1. Create a file called newcat.inc.php in the admin folder under the store folder in your WampServer www folder area.
2. Enter the following code into the new file:

Print code


<?php
   if (!isset($_SESSION['store_admin']))
   {
      echo "<h2>Sorry, you have not logged into the system</h2>\n";
      echo "<a href=\"admin.php\">Please login</a>\n";
   } else
   {
      echo "<h2>Add a new food category</h2>\n";
      echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
      echo "<form action=\"admin.php\" method=\"post\">\n";
      echo "<tr><td>New category</td><td><input type=\"text\" name=\"catname\" size=\"40\"></td></tr>\n";
      echo "</table>\n";
      echo "<input type=\"hidden\" name=\"content\" value=\"addcat\">\n";
      echo "<input type=\"submit\" value=\"Submit\">\n";
      echo "</form>\n";
   }
?>

3. Save the file and exit the editor.

First, the code checks to see if the manager is really logged into the system by checking for the store_admin session cookie (you wouldn't want anyone bypassing your security system). Since you need to use PHP code to check for the session cookie, you must stay in PHP mode to create the HTML form. You need to use PHP echo statements to produce the required HTML code for this.

If the manager is logged in, the PHP code produces a simple HTML form by echoing the required HTML code to the client's browser. This code file also demonstrates how you use an HTML table to make your HTML forms look a little more organized. The table has two columns. One contains a text label for the data entry, and the other contains the data entry textbox.

The code uses a simple textbox to input the name of the new category. The action attribute for the form points to the admin.php file (the main administration page). It also uses a hidden input field to pass along a value for the content HTML variable, which the admin.php code checks and uses for the next Web page. The hidden value points to the addcat PHP file, which will do all the work of inserting the new category into the database table.

The Add Category Web page

When the manager clicks the Submit button in the form, the addcat.inc.php include file receives the form information and must process it. Here's where things get interesting.

Processing Input Data

The PHP code in the addcat.inc.php file must retrieve the data that the form passes to it and then insert the new data into the categories table to create the new category. It sounds simple, but there's a catch. The first thing to do is retrieve the data that the form passes using the catname form data field:

$catname = $_POST['catname'];

This should look familiar to you. The challenge comes when you try to put the data into the MySQL table, because you don't really know what type of information the manager put in the form.

It's not that you don't trust the manager. You just don't know what the manager might innocently try to do. For example, look what happens if you're the manager and you try to insert the category name Produce - farmer's market using the INSERT SQL statement:

mysql>INSERT INTO categories (name) VALUES ('Produce - farmer's market');
'>

Oops, something went wrong. The MySQL console is looking for more data. Notice that the single quote you used in the data confused MySQL into thinking that the data string ended. When it saw the single quote at the end of the string, it thought you were starting another string. When you hit the ENTER key, it gave you another prompt to finish the string.

MySQL SQL statements are very picky about the format of the data. Certain characters—such as single and double quotes, backslashes, and the NUL character—cause problems in SQL statements. To solve this, you must use the MySQL escape character (the backslash) to escape (identify) these potentially dangerous characters:

mysql>INSERT INTO categories (name) VALUES ('Produce - farmer\'s market');
Query OK, 1 row affected (0.03 sec)
mysql>

That's much better. It's your job to manage and control the data used in your program. You always need to be on the lookout for characters that could break (either intentionally or by accident) your SQL statements. Fortunately, PHP provides a few tools to help you with this task.

Escaping MySQL Data in PHP

The PHP configuration file includes a controversial feature that automatically adds the escape character to all form data characters that can cause trouble in your SQL statements. The magic_quotes_gpc configuration value determines if PHP automatically adds the backslash to data received via the GET, POST, or cookie methods. If this feature is set, you don't have to worry about using the form data directly in an SQL statement.

This feature is controversial because you don't always need to have it turned on. The only time you need it is when you're pushing data into a MySQL table. For all other uses, it needlessly adds the backslash escape characters.

Because of this, there's no guarantee that your ISP has this feature turned on. This makes things somewhat confusing. You may or may not have to add the escape characters yourself in any form data, or conversely, you may or may not have to remove them if you just want to display the form data. It's up to your program to determine when you need to add the MySQL escape character or not.

This is quite a dilemma, but it has a simple solution. Follow these steps to create the addcat.inc.php code file to solve the issue.

1. Create the file addcat.inc.php in the admin folder of your store application.
2. Enter the following code into the new file:

Print code


<?php
   $catname = $_POST['catname'];
   if (get_magic_quotes_gpc())
   {
      $catname = stripslashes($catname);
   }
   $catnameval = mysql_real_escape_string($catname);
   $query="INSERT INTO categories (name) VALUES ('$catnameval')";
   $result = mysql_query($query);
   if ($result)
      echo "<h2>New category '$catname' added</h2>\n";
   else
      echo "<h2>Sorry, unable to add new category</h2>\n";
?>   

3. Save the file and exit.

The get_magic_quotes_gpc() function checks to see if the magic_quotes_gpc PHP setting is turned on for the server. If it's on, the code uses the stripshlashes() PHP function to actually remove any backslashes that the magic_quotes_gpc feature inserted into the data. This ensures that the data doesn't contain escape characters. So it can be used as-is in the application (except not for SQL queries). While this technique sounds backward, there's a reason for it.

The official PHP way to prepare data for MySQL queries is to use the mysql_real_escape_string() function. This function guarantees that the data is properly escaped and will work properly in your SQL statement. To use this feature, though, you have to be sure that the magic_quotes_gpc feature in PHP hasn't already manipulated the original data. If you determine that this feature is enabled, you must include the stripslashes() to remove the slashes added by PHP.

This is the standard technique for handling form data in PHP. Using this technique, you have access to both the clean version of the data (called $catname in the program) and the escaped version (called $catnameval). You can then use whichever version you need for your purposes. In the addcat.inc.php code, I used both versions to show you what I'm talking about.

If you add a category that includes a single quote, you'll see the single quote just fine in the Web page display, plus it'll get added to the categories table just fine. It's the best of both worlds.

In the next chapter, we'll walk through the code you need to add a new product.

Adding Products

Now that your store has a category to hold products, it's time to create some products. The code you'll use to add a new product to the store also uses a standard HTML form. However, there are a couple of twists to this. Let's create the code file first, and then we'll take a closer look at it so I can show you what I mean.

1. Create a file called newproduct.inc.php in the admin folder of your store application.
2. Enter the following code into the file:

Print code


<?php
if (!isset($_SESSION['store_admin']))
{
   echo "<h2>Sorry, you have not logged into the system</h2>\n";
   echo "<a href=\"admin.php\">Please login</a>\n";
} else
{
   $userid = $_SESSION['store_admin'];
   echo "<form enctype=\"multipart/form-data\" action=\"admin.php\" method=\"post\">\n";
   echo "<h2>Enter the new product</h2><br>\n";
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Category</td>\n";
   echo "<td><select name=\"cat\">\n";
   $query="SELECT catid,name from categories";
   $result=mysql_query($query);
   while($row=mysql_fetch_array($result,MYSQL_ASSOC))
   {
       $catid = $row['catid'];
       $name = $row['name'];
       echo "<option value=\"$catid\">$name</option>\n";
   }
   echo "</select></td></tr>\n";
   echo "<tr><td>Description</td><td><input type=\"text\" size=\"40\" name=\"description\"></td></tr>\n";
   echo "<tr><td>Price</td><td><input type=\"text\" size=\"10\" name=\"price\"></td></tr>\n";
   echo "<tr><td>Quantity in stock</td><td><input type=\"text\" size=\"10\" name=\"quantity\"></td</tr>\n";
   echo "<input type=\"hidden\" name=\"MAX_FILE_SIZE\" value=\"1024000\">\n";
   echo "<tr><td>Picture</td><td><input type=\"file\" name=\"picture\"></td></tr>\n";
   echo "</table>\n";
   echo "<input type=\"submit\" value=\"Submit\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"addproduct\">\n";
   echo "</form>\n";
}
?>

3. Save the file and exit the editor.

You should recognize most of this code. Just like when you added a category, you need to check if the session cookie is set, so you must use PHP for the entire file. The HTML form uses a table to lay out each data label and data entry item for the manager to enter new data values. To create the HTML form, you need to use two features you might not be familiar with yet.

The first feature is how you allow the manager to select which category the product belongs. The code uses a standard HTML select form item to provide a list of the available categories. The select item displays a drop-down box containing a list of items. The manager can then select one item from the list, which is what the form returns to the action file.

After declaring the <select> tag, each item is added using the <option> HTML tag. Just use the standard mysql_fetch_array() function to extract all of the category data records and create the individual <option> tags. This allows you to push as many items into the list as you need to. When it's done, you need to close out the select section using the </select> tag.

To get the values for the drop-down box, you must extract both the individual category names and catid values from the categories table and then populate the drop-down box object with that information. The category name appears in the drop-down box. When the manager selects an item, the catid value goes to the receiving program (this is the value that's stored in the product table anyway).

The second feature you may not be familiar with is the file input type.

Print code

echo "<input type=\"hidden\" name=\"MAX_FILE_SIZE\" value=\"1024000\">\n";
echo "<tr><td>Picture</td><td><input type=\"file\" name=\"picture\"></td></tr>\n";

This neat little feature in HTML shows a textbox where the user can either enter a filename or click a Browse button. When a Web site visitor clicks the button, it produces the graphical file system explorer for the PC and allows the user to browse and select the file from his or her file system. When the visitor selects a file, the filename automatically appears in the textbox.

The file input type data entry

The MAX_FILE_SIZE hidden value allows you to set a maximum size for files the manager can upload. When the manager finishes filling out the new product form and clicks the Submit button, the form action sends the data to the addproducts.inc.php file. The file input type uploads the designated file to the Web server.

Now you need to extract the file and process the image, along with the rest of the data, to store in the products table.

The addproducts Code

Let's create the addproduct.inc.php file first and then walk through what it does.

1. Create a file called addproduct.inc.php in the admin folder in your store application area.
2. Enter the following code into the file:

Print code


<?php
   $catid=$_POST['cat'];
   $description=$_POST['description'];
   $price=$_POST['price'];
   $quantity=$_POST['quantity'];
   if (get_magic_quotes_gpc())
   {
      $catid = stripslashes($catid);
      $description = stripslashes($description);
      $price = stripslashes($price);
      $quantity = stripslashes($quantity);
   }
   $catid = mysql_real_escape_string($catid);
   $description = mysql_real_escape_string($description);
   $price = mysql_real_escape_string($price);
   $quantity = mysql_real_escape_string($quantity);
   $thumbnail = getThumb($_FILES['picture']);
   $thumbnail = mysql_real_escape_string($thumbnail);
   $query = "INSERT INTO products (catid, description, picture, price, quantity) " .
            " VALUES ('$catid','$description','$thumbnail', '$price', '$quantity')";
   $result = mysql_query($query) or die('Unable to add product');
   if ($result)
      echo "<h2>New product added</h2>\n";
   else
      echo "<h2>Problem adding new product</h2>\n";
?> 

The start of the code extracts most of the form fields using the standard PHP $_POST[] array variables, then it uses our new friends get_magic_quotes_gpc(), stripslashes(), and mysql_real_escape_string() to ensure the data is in the proper format for the SQL INSERT statement.

Next, you need to handle the uploaded image file. Follow me to Chapter 4, and you'll see how to do that.

Converting and Storing Images

When the server receives the uploaded image file from the HTML form, it places it in a temporary location on the server. Your PHP code needs to retrieve the temporary file and push it into the products table BLOB data field. This requires a few different PHP features that you need to learn.

PHP provides the $_FILES[] array variable, which contains information about any files uploaded using the file input type. The $_FILES[] array is a multidimensional associative array that uses the format:

$_FILES[name][element]

The name index matches the name value used in the file input type. This is called picture in the newproduct.inc.php code. So that's the value you need to use to extract the image file from the server.

Each uploaded file has several element indexes that define specific features of the uploaded file.

To retrieve the temporary filename for the image uploaded from the file input type named picture, you would access the $_FILES['picture']['tmp_name'] array element.

The addproducts.inc.php code uses the following code to retrieve the image file:

$thumbnail = getThumb($_FILES['picture']);

This code passes the entire $_FILES['picture'] array to a PHP function called getThumb() for processing. You need to create this file next. Just follow these steps:

1. Create a file called getThumb.php in the mylibrary folder you created under your store folder.
2. Enter the following code into the file:

Print code


<?php
function getThumb($Original)
{
   if (!$Original['name'])
   {
      //no image supplied, use default
      $TempName = "images/noimage.jpg";
      $TempFile = fopen($TempName, "r");
      $thumbnail = fread($TempFile, fileSize($TempName));
   } else
   {
      //get image
      $Picture =  file_get_contents($Original['tmp_name']);
      //create image
      $SourceImage = imagecreatefromstring($Picture);
      if (!$SourceImage)
      {
         //not a valid image
        echo "Not a valid image\n";
        $TempName = "images/noimage.jpg";
        $TempFile = fopen($TempName, "r");
        $thumbnail = fread($TempFile, fileSize($TempName));
      } else
      {
         //create thumbnail
         $width = imageSX($SourceImage);
         $height = imageSY($SourceImage);
         $newThumb = imagecreatetruecolor(80, 60);
         //resize image to 80 x 60
         $result = imagecopyresampled($newThumb, $SourceImage, 
                                      0, 0, 0, 0,
                                      80, 60, $width, $height);
         //move image to variable
         ob_start();
         imageJPEG($newThumb);
         $thumbnail = ob_get_contents();
         ob_end_clean();
      }
   }
   return $thumbnail;
}?>

3. Save the file and exit the editor.
4. Edit the original admin.php file in the store folder.
5. After the include() statement for the login.php file, add a new include() function to include the getThumb.php function code file. It should now look like this:

include("/mylibrary/login.php");
include("/mylibrary/getThumb.php");

6. Save the file and exit the editor.

The job of the getThumb() function is to provide a small thumbnail version of the image the manager uploads. The getThumb() function first checks the name element of the uploaded file to see if the manager supplied a file for the image file.

If the manager didn't specify a file, you'll need to supply a generic image that you can use in the Web page:

1. Create a folder called images under the admin folder in your store application folder.
2. Take the noimage.jpg file you created back in Lesson 2 and copy it to the images folder.

The getThumbs() function reads this image using the fread() PHP function and will utilize it if the manager doesn't specify an image or if it can't convert the image the manager does specify.

If the manager specified a file image, the getThumbs() function uses the file_get_contents() function to read the temporary file on the server (specified using the tmp_name element) into a PHP string variable, then attempts to create an image from the image string. If that's successful, it uses the GD2 library functions to resample the image to an 80 x 60-pixel thumbnail. This ensures that all of our product images are the same size, making our catalog layout better.

After creating the new thumbnail, the getThumbs() function code needs to place it back into a PHP variable to pass to the calling program. This gets a little tricky. You'll notice the small section of code that performs this function:

ob_start();
imageJPEG($newThumb);
$thumbnail = ob_get_contents();
ob_end_clean();

Unfortunately, there's no function that converts an image back into a string value that you can pass to the calling program. You have to use some PHP tricks to do that.

The ob_start() function creates an output buffer that stores anything that would've normally been output from the PHP code. Once the PHP sees the ob_start() function, it creates a temporary buffer area in memory and redirects all output to that buffer instead of sending the output directly to the client browser. The imageJPEG() function normally outputs the image to the browser, but now, PHP redirects that output to the buffer area.

The ob_get_contents() function retrieves the contents of the buffer area. You use this to store the image value into a string PHP variable. To stop the buffering, you must use the ob_end_clean() function. After you close the output buffer, the output from the PHP code returns to normal and goes to the client's browser.

Now, the newly formed image (or our no image available image) is in the $thumbnail variable. The function then returns it back to the calling program.

Now back in the addproduct.inc.php file, you need to use the mysql_real_escape_string() function on the thumbnail image in case there are any stray quotes or backslashes in the binary data. Then you're ready to use the INSERT statement to push in your new product.

Wow, that was a lot of work to do in one day! Let's move on to the Summary and wrap things up.

Summary

Today you accomplished quite a bit of work on the Food Store application. You learned how to handle HTML form data properly before pushing it into your MySQL tables. You must always check for quotes and backslashes that can cause problems in your SQL statements. The get_magic_quotes_gpc() function is used to determine if the PHP server has already inserted backslashes to escape any data.

Next, you discovered how to use the HTML select form item, which creates a drop-down box containing your store categories. It's easy to populate the drop-down box items with data from a table— just use the <option> tag to identify each data record from the table. You then saw how to utilize the HTML file input type to allow managers to browse for image files on the PC to upload to your Web site.

You then determined that after receiving the product information you need to push it into the database by using the $_FILES [] array variable. And finally, you created a thumbnail image from an image file.

Now that you have products in your store, you'll want to be able to manage them by either editing product information or removing products from the catalog. We'll work on that in the next lesson.